﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ProjectProperties;


namespace BusinessLayer
{
    public class ProductSQL
    {
        #region Private Variables
        private SqlConnection sqlCone;
        private DataSet dsRetrn;
        #endregion

        public ProductSQL()
        {
            sqlCone = new SqlConnection("Data Source:SYS2\\SQLEXPRESS;Intial Catalog=Sample;Integrated Security=true");
            dsRetrn = new DataSet();
        }

        ~ProductSQL()
        {

        }

        public DataSet DisplayTop100Products()
        {
            SqlCommand oSqlCommand = new SqlCommand();
            oSqlCommand.Connection = sqlCone;
            oSqlCommand.CommandText = "Company_DisplayTop100";
            oSqlCommand.CommandType = CommandType.StoredProcedure;
            //sqlCone.Open();
            oSqlCommand.ExecuteNonQuery();
            //sqlCone.Close();
            using (SqlDataAdapter daAdapter = new SqlDataAdapter(oSqlCommand))
            {
                dsRetrn = new DataSet();
                daAdapter.Fill(dsRetrn);
                oSqlCommand.Parameters.Clear();
            }
            return dsRetrn;
        }

        public DataSet SelectTop100Company(CompanyInfo oCompanyInfo)
        {
            SqlCommand oSqlCommand = new SqlCommand();
            oSqlCommand.Connection = sqlCone;
            oSqlCommand.CommandText = "Company_SelectTop100";
            oSqlCommand.CommandType = CommandType.StoredProcedure;
            oSqlCommand.Parameters.AddWithValue("@Company_Name", oCompanyInfo.Company_Name);
            //sqlCone.Open();
            oSqlCommand.ExecuteNonQuery();
            //sqlCone.Close();
            using (SqlDataAdapter daAdapter = new SqlDataAdapter(oSqlCommand))
            {
                dsRetrn = new DataSet();
                daAdapter.Fill(dsRetrn);
                oSqlCommand.Parameters.Clear();
            }
            return dsRetrn;
        }

        public void InsertCompany(CompanyInfo oCompanyInfo)
        {
            SqlCommand oSqlCommand = new SqlCommand();
            oSqlCommand.Connection = sqlCone;
            oSqlCommand.CommandText = "Company_InsertCompany";
            oSqlCommand.CommandType = CommandType.StoredProcedure;
            oSqlCommand.Parameters.AddWithValue("@Company_Name", oCompanyInfo.Company_Name);
            sqlCone.Open();
            oSqlCommand.ExecuteNonQuery();
            sqlCone.Close();
        }

        public void UpdateCompany(CompanyInfo oCompanyInfo)
        {
            SqlCommand oSqlCommand = new SqlCommand();
            oSqlCommand.Connection = sqlCone;
            oSqlCommand.CommandText = "Company_UpdateCompany";
            oSqlCommand.CommandType = CommandType.StoredProcedure;
            oSqlCommand.Parameters.AddWithValue("@Company_Id", oCompanyInfo.Company_Id);
            oSqlCommand.Parameters.AddWithValue("@Company_Name", oCompanyInfo.Company_Name);
            sqlCone.Open();
            oSqlCommand.ExecuteNonQuery();
            sqlCone.Close();
        }
    }
}
